-- This is a demo for UTF-8 collations.
-- © Erland Sommarskog 2025.

-- We keep on using the Welsh database from the previous demo.
USE Welsh_100_CS_AS_SC
go
-- Compare nvarchar and varchar. Only the first word looks the same. Characters
-- not in Latin-1 are replaced by fallbacks.
SELECT N'Räksmörgås', N'ćevapčići', N'中目黒', N'Ｐａｒｉｓ', N'𐌰𐌱𐌲𐌳𐌴😕'
SELECT  'Räksmörgås',  'ćevapčići',  '中目黒',  'Ｐａｒｉｓ',  '𐌰𐌱𐌲𐌳𐌴😕'
go
-- Now create a UTF8 datbase for Finnish and Swedish.
CREATE DATABASE Welsh_100_CS_AS_SC_UTF8 COLLATE Welsh_100_CS_AS_SC_UTF8 
go
USE Welsh_100_CS_AS_SC_UTF8 
go
-- Now all words come through correctly, also in varchar!
SELECT 'Räksmörgås', 'ćevapčići', '中目黒', 'Ｐａｒｉｓ', '𐌰𐌱𐌲𐌳𐌴😕'
go
-- Add some words to a table, and look at lengths in characters and bytes.
CREATE TABLE words (no int NOT NULL, word varchar(50) NOT NULL)
INSERT words (no, word)
   VALUES(1, 'English'), (2, 'Räksmörgås'), (3, 'ćevapčići'), 
         (4, '中目黒'), (5, 'Ｐａｒｉｓ'), (6, '𐌰𐌱𐌲𐌳𐌴😕')
go
SELECT no, word, len(word) as charlen, datalength(word) AS bytelen,
       substring(word, 1, 3) AS FirstThree
FROM   words
ORDER BY no
go
-- "Räksmörgås" is ten characters. Does that fit in varchar(10)? By now, 
-- you should know what to expect.
CREATE TABLE varchar10(sandwich varchar(10) NOT NULL)
INSERT varchar10(sandwich) VALUES ('räksmörgås')

  
